import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class DBTestTechData {

	private Connection connection;

	@Before
	public void connect()
	{
		try {
			connection = DriverManager.getConnection("jdbc:h2:tcp://localhost/activiti", "sa", "");
		} catch (SQLException e) {
			e.printStackTrace();
			fail();
		}
	}

	/*
	 * Preconditions
	 * Existing Table: TECH_DATA
	 * Existing Inserts: 
	 * 1. insert into TECH_DATA values ('Beton', 15.0, 'm^3',15.0, 0.0, 0.0,'TestProject')
	 * 2. insert into TECH_DATA values ('Bauholz', 12.0, 'Balken', 24.0, 0.0, 0.0, 'TestProject')
	 */
	@Test
	public void selectAllData()
	{
		try
		{
			PreparedStatement ps=connection.prepareStatement("select * from TECH_DATA where PROJECTID_='TestProject'");
			ResultSet rs = ps.executeQuery();

			ArrayList<String> partners = new ArrayList<String>();
			while(rs.next()){
				partners.add(rs.getString("NAME_"));
			}

			assertEquals(2, partners.size());

			rs.close();
			ps.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
			fail();
		}
	}

	@Test
	public void testSingleSelect()
	{
		try
		{
			PreparedStatement ps=connection.prepareStatement("select * from TECH_DATA where PROJECTID_='TestProject' AND NAME_='Bauholz'");
			ResultSet rs=ps.executeQuery();

			if(rs.next())
			{
				assertEquals("Bauholz", rs.getString("NAME_"));
				assertEquals("12.0", ""+rs.getDouble("ORDERED_AMOUNT_"));
				assertEquals("Balken", rs.getString("UNIT_"));
				assertEquals("24.0", ""+rs.getDouble("EXPECTED_PRICE_"));
				assertEquals("0.0", ""+rs.getDouble("DELIVERED_AMOUNT_"));
				assertEquals("0.0", ""+rs.getDouble("REAL_PRICE_"));
			}

			rs.close();
			ps.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
			fail();
		}
	}

	@Test
	public void testInsert() {
		try {
			/*
			 * Insert new Data
			 */
			PreparedStatement ps = connection.prepareStatement("insert into TECH_DATA values('Fenster', 5.0, 'Stk', 10.0, 0.0, 0.0, 'TestProject');");
			ps.execute();
			ps.close();

			/*
			 * Test if insert was successful
			 */
			ps = connection.prepareStatement("select * from TECH_DATA where PROJECTID_='TestProject' AND NAME_='Fenster'");
			ResultSet rs = ps.executeQuery();

			if(rs.next())
			{
				assertEquals("Fenster", rs.getString("NAME_"));
				assertEquals("5.0", ""+rs.getDouble("ORDERED_AMOUNT_"));
				assertEquals("Stk", rs.getString("UNIT_"));
				assertEquals("10.0", ""+rs.getDouble("EXPECTED_PRICE_"));
				assertEquals("0.0", ""+rs.getDouble("DELIVERED_AMOUNT_"));
				assertEquals("0.0", ""+rs.getDouble("REAL_PRICE_"));
			}

			rs.close();
			ps.close();

		} catch (SQLException e) {
			e.printStackTrace();
			fail();
		}
	}

	@Test
	public void updateEntry(){
		try {
			PreparedStatement ps = connection.prepareStatement("update TECH_DATA set DELIVERED_AMOUNT_ = 9.0, REAL_PRICE_ = 22.0 where PROJECTID_='TestProject' AND NAME_='Fenster';");
			ps.execute();
			ps.close();

			ps = connection.prepareStatement("select * from TECH_DATA where PROJECTID_='TestProject' AND NAME_='Fenster'");
			ResultSet rs = ps.executeQuery();

			if(rs.next())
			{
				assertEquals("Fenster", rs.getString("NAME_"));
				assertEquals("5.0", ""+rs.getDouble("ORDERED_AMOUNT_"));
				assertEquals("Stk", rs.getString("UNIT_"));
				assertEquals("10.0", ""+rs.getDouble("EXPECTED_PRICE_"));
				assertEquals("9.0", ""+rs.getDouble("DELIVERED_AMOUNT_"));
				assertEquals("22.0", ""+rs.getDouble("REAL_PRICE_"));
			}

			rs.close();
			ps.close();

		} catch (SQLException e) {
			e.printStackTrace();
			fail();
		}
	}

	@Test
	public void deleteEntry()
	{
		try {
			PreparedStatement ps = connection.prepareStatement("delete from TECH_DATA where PROJECTID_='TestProject' AND NAME_='Fenster';");
			ps.execute();
			ps.close();

			ps = connection.prepareStatement("select * from TECH_DATA where PROJECTID_='TestProject' AND NAME_='Fenster';");
			ResultSet rs = ps.executeQuery();

			assertEquals(false, rs.next());

			rs.close();
			ps.close();

		} catch (SQLException e) {
			e.printStackTrace();
			fail();
		}
	}

	@After
	public void closeConnection()
	{
		try
		{
			connection.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			fail();
		}
	}
}
